﻿using Gumini.JulaDB.Enums;
using Gumini.JulaDB.Provider;
using Gumini.JulaDB.Test.Common;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Gumini.JulaDB.Test
{
    /// <summary>
    /// A set of tests for various SELECT queries.
    /// </summary>
    [TestClass]
    public class SelectTest
    {
        private static JulaDbConnection _connection;
        private static PersonTestDataCreator _peopleData;

        [ClassInitialize]
        public static void InitializeDatabase(TestContext context)
        {
            _connection = TestUtils.CreateTestDatabase();
            _peopleData = new PersonTestDataCreator(_connection);
        }

        [ClassCleanup]
        public static void CleanupDatabase()
        {
            _connection.Close();
        }

        /// <summary>
        /// A simplest possible select of all rows from a table.
        /// </summary>
        [TestMethod]
        public void SelectAllSimple()
        {
            var dataTable = _connection.ExecuteValidSelect(@"select * from person");

            Assert.AreEqual(_peopleData.TotalPersonCount, dataTable.Rows.Count);
            dataTable.CheckColumnNames("Id", "FirstName", "LastName", "Age");
        }

        /// <summary>
        /// A select from a single table with a WHERE clause.
        /// </summary>
        [TestMethod]
        public void SelectByLastName()
        {
            var dataTable = _connection.ExecuteValidSelect(@"select * from person where lastname = 'Kowalski'");

            Assert.AreEqual(3, dataTable.Rows.Count);
            dataTable.CheckColumnNames("Id", "FirstName", "LastName", "Age");
        }

        /// <summary>
        /// A select from a single table with row values in a particular range.
        /// </summary>
        [TestMethod]
        public void SelectByAge()
        {
            var dataTable = _connection.ExecuteValidSelect(@"select * from person where age > 20 and age <= 60");

            Assert.AreEqual(3, dataTable.Rows.Count);
            dataTable.CheckColumnNames("Id", "FirstName", "LastName", "Age");
        }

        /// <summary>
        /// A test for DISTINCT keyword to get unique values.
        /// </summary>
        [TestMethod]
        public void SelectDistinct()
        {
            var dataTable = _connection.ExecuteValidSelect(@"select distinct FirstName from person");

            Assert.AreEqual(5, dataTable.Rows.Count);
            dataTable.CheckColumnNames("FirstName");
        }

        /// <summary>
        /// A select with a row-based expression.
        /// </summary>
        [TestMethod]
        public void SelectRowExpression()
        {
            var dataTable = _connection.ExecuteValidSelect("select FirstName, 2 * age as DoubleAge from person");

            Assert.AreEqual(_peopleData.TotalPersonCount, dataTable.Rows.Count);
            dataTable.CheckColumnNames("FirstName", "DoubleAge");

            var row = dataTable.Rows[0];
            Assert.AreEqual(12.4M * 2, (decimal)row[1]);
        }

        /// <summary>
        /// Selecting values from a nonexistent column should cause an error.
        /// </summary>
        [TestMethod]
        public void SelectUnknownColumn()
        {
            _connection.ExecuteInvalidCommand(@"select InvalidName from person", ErrorCode.UnknownIdentifier);
        }

        /// <summary>
        /// Select with a WHERE condition using a LIKE operator to check prefix.
        /// </summary>
        [TestMethod]
        public void SelectLikeLastNamePercentage()
        {
            var dataTable = _connection.ExecuteValidSelect("select * from person where LastName like 'Kowalsk%'");

            Assert.AreEqual(4, dataTable.Rows.Count);
        }

        /// <summary>
        /// Select with a WHERE condition using LIKE and underscores.
        /// </summary>
        [TestMethod]
        public void SelectLikeLastNameUnderscore()
        {
            var dataTable = _connection.ExecuteValidSelect("select * from person where LastName like '__wa_'");

            Assert.AreEqual(3, dataTable.Rows.Count);
        }

        /// <summary>
        /// A check that table names are case-insensitive.
        /// </summary>
        [TestMethod]
        public void TableNameCaseInsensivity()
        {
            var dataTable = _connection.ExecuteValidSelect("select * from pErSoN");

            Assert.AreEqual(_peopleData.TotalPersonCount, dataTable.Rows.Count);
        }

        /// <summary>
        /// A simple select using table alias.
        /// </summary>
        [TestMethod]
        public void SelectByTableAlias()
        {
            var dataTable = _connection.ExecuteValidSelect("select p.Age from Person p");

            Assert.AreEqual(_peopleData.TotalPersonCount, dataTable.Rows.Count);
        }

        /// <summary>
        /// A test for IS NULL checking.
        /// </summary>
        [TestMethod]
        public void SelectWhereNull()
        {
            _connection.ExecuteValidScalar("select count(*) from person where person.Age is null", _peopleData.TotalPersonCount - _peopleData.AgeNotNullPersonCount);
        }

        /// <summary>
        /// A test for IS NOT NULL checking.
        /// </summary>
        [TestMethod]
        public void SelectWhereNotNull()
        {
            _connection.ExecuteValidScalar("select count(*) from person where person.Age is not null", _peopleData.AgeNotNullPersonCount);
        }

        /// <summary>
        /// An invalid query with WHERE condition evaluating to non-boolean value.
        /// </summary>
        [TestMethod]
        public void SelectInvalidWhere()
        {
            _connection.ExecuteInvalidCommand("select count(*) from person where person.Age * 2", ErrorCode.TypeError);
        }

        /// <summary>
        /// A select with a nontrivial row-based WHERE condition.
        /// </summary>
        [TestMethod]
        public void SelectLastNameNotLongerThanFirstName1()
        {
            _connection.ExecuteValidScalar(
              @"select count(*)
                from person
                where len(person.FirstName) >= len (LastName)", 1);
        }

        /// <summary>
        /// A select with a nontrivial row-based WHERE negated condition.
        /// </summary>
        [TestMethod]
        public void SelectLastNameNotLongerThanFirstName2()
        {
            _connection.ExecuteValidScalar(
              @"select count(*)
                from person
                where not (len(person.FirstName) < len (LastName))", 1);
        }
    }
}
